IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetStandardReportList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetStandardReportList]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO                      
/******************************************************************      
* Name:   [GetStandardReportList]      
* Purpose:  Retrieves matching rows joining with passed in admPersonIds.       
* Note: This procedure is not used anywhere in the COI application as stadard reports has been removed.     
* PARAMETERS(IN)      
* Name          Description           
* -------------      -------------------------------------------      
* @xml       xml Document containing list of Ids(REQUIRED)        
*********************************************************************/      
      
CREATE PROCEDURE [dbo].[GetStandardReportList]      
(      
@xml text      
)      
AS BEGIN      
SET NOCOUNT ON      
      
 declare @xmlDoc int        
 declare @admPersonIds table (Sequence int identity, [ID] int)        
 exec sp_xml_preparedocument @xmlDoc output, @xml        
 insert into @admPersonIds select [PersonId] from openxml(@xmlDoc, '//IdList', 2) with (PersonId int)        
 exec sp_xml_removedocument @xmlDoc      
      
SELECT adp.[Id],      
    adp.[lastname]+', '+adp.[firstname] as [Name],      
       adp1.[lastname]+', '+adp1.[firstName] as [Supervisor],  
    adp.Title,    
    coi.[Id] as [COIId],    
       coi.[PolicyViolation] ,      
       coi.[HasOutsideActivities] ,      
       coi.[HasEquityInterests] ,      
       coi.[HasFamilyAssociations] ,      
       coi.[OtherActivity] ,      
       coi.[DateSubmitted]             
     FROM @admPersonIds tempTable  
  Left JOIN AdmPerson adp   
  on tempTable.[Id] = adp.[Id]  
  Left Outer JOIN ConflictOfInterest coi   
  on tempTable.[Id]= coi.[admPersonId]  
  AND coi.[Id]= (Select Max(Id)as LatestCOIId from conflictofinterest where admPersonId=adp.[Id])  
 Left Outer Join  COI_Hierarchy_Map chm  
  on adp.[Id]= chm.[childId]  
    Left Outer Join AdmPerson adp1   
  on adp1.[Id]= chm.[ParentId]     
       
       
END  
  